from src.db.base import Base
import logging
from datetime import datetime
from src.config import LoggerConfig

# 配置日志
LoggerConfig.configure_logger('log/sn-audit-report.log')

class Crud(Base):
    def __init__(self, table_name):
        super().__init__()
        self.table_name = table_name
        self.logging = logging.getLogger('Crud')

    def get_all_records(self):
        """
        获取所有记录
        :return: 返回所有记录的信息列表
        """
        try:
            query = f"SELECT * FROM {self.table_name}"
            cursor = self.execute_query(query)
            return cursor.fetchall()
        except Exception as e:
            self.logging.error(f"获取所有记录时发生错误: {e}", exc_info=True)
            return []

    def get_records_by_condition(self, condition, params):
        """
        根据条件查询记录
        :param condition: 查询条件
        :param params: 查询参数
        :return: 返回符合条件的记录信息列表
        """
        try:
            query = f"SELECT * FROM {self.table_name} WHERE {condition}"
            cursor = self.execute_query(query, params)
            return cursor.fetchall()
        except Exception as e:
            self.logging.error(f"根据条件查询记录时发生错误: {e}", exc_info=True)
            return []

    def get_records_with_join(self, join_condition, select_fields, join_tables, where_condition=None, params=None):
        """
        根据连接条件查询记录
        :param join_condition: 连接条件
        :param select_fields: 选择的字段
        :param join_tables: 需要连接的表
        :param where_condition: 查询条件
        :param params: 查询参数
        :return: 返回符合条件的记录列表
        """
        try:
            query = f"SELECT {select_fields} FROM {self.table_name} {join_tables} ON {join_condition}"
            if where_condition:
                query += f" WHERE {where_condition}"

            cursor = self.execute_query(query, params)
            return cursor.fetchall()
        except Exception as e:
            self.logging.error(f"根据连接条件查询记录时发生错误: {e}", exc_info=True)
            return []

    def update_records_by_condition(self, set_clause, condition, params):
        """
        根据条件更新记录
        :param set_clause: 更新的SET子句
        :param condition: 更新的WHERE子句
        :param params: SQL查询的参数
        :return: 更新是否成功
        """
        try:
            update_query = f"UPDATE {self.table_name} SET {set_clause} WHERE {condition}"

            cursor = self.execute_query(update_query, params)
            self.connection.commit()

            return cursor.rowcount > 0  # 检查是否有记录被更新
        except Exception as e:
            self.logging.error(f"更新记录时发生错误: {e}", exc_info=True)
            return False


class ReportDetails():
    def __init__(self):
        """
        初始化 ReportDetails 类，设置表名并创建 Curd 实例
        :param table_name: 数据库表名
        """
        self.crud = Crud('dbo.C02Report')
        self.logging = logging.getLogger('ReportDetails')

    def get_detailinfo(self):
        """
        获取详细信息：查询 c02ReportNumber 字段包含 '司农' 且 c02ORNumber 字段为 '报表歪' 的所有数据
        :return: 包含查询结果的列表
        """
        try:
            # 设置查询条件，查询 c02ReportNumber 字段包含 '司农' 且 c02ORNumber 字段为 '报表歪' 的所有数据
            report_number_pattern = '司农%'  # '司农' 开头的报告编号
            or_number_value = '报表歪'  # 固定的 c02ORNumber 值
            condition = "c02ReportNumber LIKE %s AND c02ORNumber = %s AND c02T = %s AND c02BBState = '数据齐可报备'"  # SQL 查询条件
            params = (report_number_pattern, or_number_value, 'Z00004')  # 查询参数

            # 记录查询条件
            self.logging.info(
                f"查询条件: c02ReportNumber LIKE '{report_number_pattern}' AND c02ORNumber = '{or_number_value}'")

            # 执行查询，获取记录
            records = self.crud.get_records_by_condition(condition, params)

            # 初始化结果列表，保存每条记录的详细信息
            query_list = []
            for record in records:
                c02_code_value = record['c02Code']
                # 获取并添加每条记录的详细信息
                query_list.append(self.get_report_info(c02_code_value))

            return query_list
        except Exception as e:
            self.logging.error(f"查询时发生错误: {e}", exc_info=True)  # exc_info=True会增加栈的追溯

    def get_report_info(self, c02_code_value):
        """
        获取报告详细信息
        :param c02_code_value: c02Code 值
        :return: 包含报告详细信息的字典
        """
        try:
            # 设置查询条件，根据 c02Code 查找详细信息
            condition = "c02Code = %s"
            params = (c02_code_value)

            # 记录查询信息
            self.logging.info(f"查询报告编号为 '{c02_code_value}' 的记录")

            # 执行查询，获取记录
            records = self.crud.get_records_by_condition(condition, params)
            for record in records:
                # 提取并处理报告详细信息
                original_report_type = record['c02ReportName']
                # 去除日期区间
                if ']' in original_report_type:
                    original_report_type = original_report_type.split(']', 1)[1]

                # 去除方括号及其中的内容
                core_info = []
                in_bracket = False
                for char in original_report_type:
                    if char == '[':
                        in_bracket = True
                    elif char == ']':
                        in_bracket = False
                    elif not in_bracket:
                        core_info.append(char)

                # 将处理后的字符拼接成新的字符串，并去除两端的空白
                c02ReportName = ''.join(core_info).strip()

                c02CusName = record['c02CusName']  # 被审计单位名称
                ReportNumber = record['c02ReportNumber']  # 报告文号
                c02OpinionType = record['c02OpinionType']  # 审计意见类型
                c02InvoiceValue = record['c02InvoiceValue']  # 审计意见类型
                c02FilePath = record['c02ROPath2']

                # 报告开始日期，只取年月日
                c02RStartDate = datetime.strptime(str(record['c02RStartDate']), "%Y-%m-%d %H:%M:%S").strftime("%Y-%m-%d")
                # 报告结束日期，只取年月日
                c02REndDate = datetime.strptime(str(record['c02REndDate']), "%Y-%m-%d %H:%M:%S").strftime("%Y-%m-%d")
                # 报告日期
                c02ReportDate = datetime.strptime(str(record['c02ReportDate']), "%Y-%m-%d %H:%M:%S").strftime("%Y-%m-%d")

                # 记录报告详细信息
                self.logging.info(
                    f"报告类型: {c02ReportName}, 被审计单位名称: {c02CusName}, 报告文号: {ReportNumber}, 审计意见类型: {c02OpinionType}, 报告开始日期: {c02RStartDate}, 报告结束日期: {c02REndDate}")

                # 收集并返回报告详细信息
                result = {
                    "报告id": record['c02Id'],
                    "报告编号": c02_code_value,
                    "报告类型": c02ReportName,
                    "被审计单位名称": c02CusName,
                    "报告文号": ReportNumber,
                    "审计意见类型": c02OpinionType,
                    "报告开始日期": c02RStartDate,
                    "报告结束日期": c02REndDate,
                    "报告日期": c02ReportDate,
                    "审计收费": c02InvoiceValue,
                    "统一社会信用代码": self.get_LicenceNum(c02_code_value),
                    "资产信息": self.get_Assets(c02_code_value),
                    "签字注册会计师": self.get_n04Name(c02_code_value),
                    "公司注册地和业务所在地": self.get_b03RegAddress1(c02_code_value),
                    "审计报告文件名称": c02FilePath,
                    "业务类型": self.get_c91ReportType(c02_code_value)
                }
                return result
        except Exception as e:
            self.logging.error(f"查询报告信息时发生错误: {e}", exc_info=True)

    def get_LicenceNum(self, c02_code_value):
        """
        获取统一社会信用代码
        :param c02_code_value: c02Code 值
        :return: 统一社会信用代码
        """
        try:
            # 设置查询字段，选取 c02Code 和 b03LicenceNum 字段
            select_fields = "C02Report.c02Code, B03Customer.b03LicenceNum"
            # 设置需要连接的表，连接 B03Customer 表
            join_tables = "JOIN dbo.B03Customer"
            # 设置连接条件，C02Report 表的 c02B03Id 和 c02T 字段与 B03Customer 表的 b03Id 和 b03T 字段匹配
            join_condition = "C02Report.c02B03Id = B03Customer.b03Id AND C02Report.c02T = B03Customer.b03T"
            # 设置查询条件，仅选择 c02Code 匹配的记录
            where_condition = "C02Report.c02Code = %s"
            params = (c02_code_value)

            # 执行查询，获取记录
            records = self.crud.get_records_with_join(join_condition, select_fields, join_tables, where_condition,params)
            for record in records:
                if 'b03LicenceNum' in record:
                    licence_num = record['b03LicenceNum']
                    self.logging.info(f"统一社会信用代码为: {licence_num}")
                    return licence_num
        except Exception as e:
            self.logging.error(f"查询统一社会信用代码时发生错误: {e}", exc_info=True)

    def get_Assets(self, c02_code_value):
        """
        获取资产信息
        :param c02_code_value: c02Code 值
        :return: 包含资产信息的字典
        """
        try:
            # 设置查询字段，选取 c02Code、c21C92Name 和 c21Value 字段
            select_fields = "C02Report.c02Code, C21ReportOutData.c21C92Name,C21ReportOutData.c21Value"
            # 设置需要连接的表，连接 C21ReportOutData 表
            join_tables = "JOIN dbo.C21ReportOutData"
            # 设置连接条件，C02Report 表的 c02Code 字段与 C21ReportOutData 表的 c21C02Code 字段匹配
            join_condition = "C02Report.c02Code = C21ReportOutData.c21C02Code"
            # 设置查询条件，仅选择 c02Code 匹配的记录
            where_condition = "C02Report.c02Code = %s"
            params = (c02_code_value)

            # 执行查询，获取记录
            records = self.crud.get_records_with_join(join_condition, select_fields, join_tables, where_condition,params)
            assets_info = {}  # 初始化资产信息字典
            for record in records:
                c21C92Name = record['c21C92Name']
                c21Value = record['c21Value']
                # 将资产信息添加到字典中
                assets_info[c21C92Name] = c21Value
                self.logging.info(f"{c21C92Name}的值为: {c21Value}")
            return assets_info
        except Exception as e:
            self.logging.error(f"查询资产信息时发生错误: {e}", exc_info=True)

    def get_n04Name(self, c02_code_value):
        """
        获取签字注册会计师名单
        :param c02_code_value: c02Code 值
        :return: 包含签字注册会计师姓名的列表
        """
        try:
            # 设置查询字段，选取 c02Code 和 n04Name 字段
            select_fields = "C02Report.c02Code, N04Account.n04Name"
            # 设置需要连接的表，连接 N04Account 表
            join_tables = "JOIN dbo.N04Account"
            # 设置连接条件，C02Report 表的 c02Signatory1 和 c02Signatory2 字段与 N04Account 表的 n04Id 字段匹配，并且 c02T 字段与 n04T 字段匹配
            join_condition = "(C02Report.c02Signatory1 = N04Account.n04Id AND C02Report.c02T = N04Account.n04T) OR (C02Report.c02Signatory2 = N04Account.n04Id AND C02Report.c02T = N04Account.n04T)"
            # 设置查询条件，仅选择 c02Code 匹配的记录
            where_condition = "C02Report.c02Code = %s"
            params = (c02_code_value,)

            # 执行查询，获取记录
            records = self.crud.get_records_with_join(join_condition, select_fields, join_tables, where_condition,params)
            signatories = []  # 初始化签字注册会计师列表
            for record in records:
                if 'n04Name' in record:
                    n04_name = record['n04Name']
                    self.logging.info(f"签字注册会计师: {n04_name}")
                    signatories.append(n04_name)
            return signatories
        except Exception as e:
            self.logging.error(f"查询签字注册会计师时发生错误: {e}", exc_info=True)

    def get_b03RegAddress1(self, c02_code_value):
        """
        获取公司注册地和业务所在地
        :param c02_code_value: c02Code 值
        :return: 公司注册地和业务所在地
        """
        try:
            # 设置查询字段，选取 c02B03Id、c02T 和 b03RegAddress1 字段
            select_fields = "C02Report.c02B03Id, C02Report.c02T, B03Customer.b03RegAddress1"
            # 设置需要连接的表，连接 B03Customer 表
            join_tables = "JOIN dbo.B03Customer"
            # 设置连接条件，C02Report 表的 c02B03Id 和 c02T 字段与 B03Customer 表的 b03Id 和 b03T 字段匹配
            join_condition = "C02Report.c02B03Id = B03Customer.b03Id AND C02Report.c02T = B03Customer.b03T"
            # 设置查询条件，仅选择 c02Code 匹配的记录
            where_condition = "C02Report.c02Code = %s"
            params = (c02_code_value,)

            # 执行查询，获取记录
            records = self.crud.get_records_with_join(join_condition, select_fields, join_tables, where_condition,params)
            for record in records:
                if 'b03RegAddress1' in record:
                    b03_reg_address1 = record['b03RegAddress1']
                    self.logging.info(f"公司注册地和业务所在地: {b03_reg_address1}")
                    return b03_reg_address1
        except Exception as e:
            self.logging.error(f"查询公司注册地时发生错误: {e}", exc_info=True)

    def get_c91ReportType(self, c02_code_value):
        """
        获取业务报告类型
        :param c02_code_value: c02Code 值
        :return: 业务报告类型
        """
        try:
            # 设置查询字段，选取 c02C91Code、c02T 和 c91ReportType 字段
            select_fields = "C02Report.c02C91Code, C02Report.c02T, C91BusinessType.c91ReportType"
            # 设置需要连接的表，连接 C91BusinessType 表
            join_tables = "JOIN dbo.C91BusinessType"
            # 设置连接条件，C02Report 表的 c02C91Code 和 c02T 字段与 C91BusinessType 表的 c91Code 和 c91T 字段匹配
            join_condition = "C02Report.c02C91Code = C91BusinessType.c91Code AND C02Report.c02T = C91BusinessType.c91T"
            # 设置查询条件，仅选择 c02Code 匹配的记录
            where_condition = "C02Report.c02Code = %s"
            params = (c02_code_value,)

            # 执行查询，获取记录
            records = self.crud.get_records_with_join(join_condition, select_fields, join_tables, where_condition,params)
            for record in records:
                if 'c91ReportType' in record:
                    c91_report_type = record['c91ReportType']
                    self.logging.info(f"业务报告类型: {c91_report_type}")
                    return c91_report_type
        except Exception as e:
            self.logging.error(f"查询业务报告类型时发生错误: {e}", exc_info=True)

    def update_log(self, target_c02ReportNumber):
        """
        更新日志内容，追加新日志条目
        :param target_c02ReportNumber: 目标 c02ReportNumber 的值
        :return: 更新后的日志内容
        """
        try:
            # 从数据库中读取现有日志
            query = "SELECT c02Log FROM C02Report WHERE c02ReportNumber = %s"
            cursor = self.crud.execute_query(query, (target_c02ReportNumber,))
            existing_log = cursor.fetchall()
            if not existing_log:
                self.logging.error(f"无法找到报告编号为 {target_c02ReportNumber} 的记录")
                return None

            # 获取当前日志内容
            existing_log_content = existing_log[0]['c02Log'] if existing_log else ""
            print(existing_log_content)

            # 获取当前时间
            current_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            new_log_entry = f"[管理员 {current_time}]已对外报备\r\n"
            updated_log = existing_log_content + new_log_entry
            return updated_log
        except Exception as e:
            self.logging.error(f"更新日志时发生错误: {e}", exc_info=True)
            return None

    def update_c02_report(self, new_c02ORNumber, new_c02BBState, target_c02ReportNumber, update_log_flag=True):
        """
        更新 C02Report 表中的记录
        :param new_c02ORNumber: 要设置的 c02ORNumber 的新值
        :param new_c02BBState: 要设置的 c02BBState 的新值
        :param target_c02ReportNumber: 目标 c02ReportNumber 的值
        :param update_log_flag: 是否需要更新日志信息
        :return: 更新是否成功
        """
        try:
            # 确保参数不为空
            if new_c02ORNumber is None or target_c02ReportNumber is None:
                self.logging.error("传入的参数不能为None")
                return False

            # 获取当前时间
            current_time = datetime.now()

            # 生成新的日志信息（如果需要）
            new_log = None
            if update_log_flag:
                new_log = self.update_log(target_c02ReportNumber)
                if new_log is None:
                    self.logging.error("无法生成新的日志内容")
                    return False
            else:
                query = "SELECT c02Log FROM C02Report WHERE c02ReportNumber = %s"
                cursor = self.crud.execute_query(query, (target_c02ReportNumber,))
                existing_log = cursor.fetchall()
                new_log = existing_log[0]['c02Log'] if existing_log else ""

            # 设置更新的SET子句和WHERE子句
            set_clause = """
                c02ORNumber = %s,
                c02BBState = %s,
                c02ORStaff = %s,
                c02ORDate = %s,
                c02Log = %s
            """
            condition = "c02ReportNumber = %s"
            update_params = (
                new_c02ORNumber,
                new_c02BBState,
                'U000000',  # 固定的 c02ORStaff 值
                current_time,
                new_log,
                target_c02ReportNumber
            )

            # 记录更新条件和参数
            self.logging.info(f"更新条件: c02ReportNumber = {target_c02ReportNumber} 带参数: {update_params}")

            # 调用通用更新方法
            return self.crud.update_records_by_condition(set_clause, condition, update_params)
        except Exception as e:
            self.logging.error(f"更新记录时发生错误: {e}", exc_info=True)
            return False


if __name__ == "__main__":
    # 创建 ReportDetails 实例
    report_details = ReportDetails()
    report_details.update_c02_report(
        new_c02ORNumber='报表歪',
        new_c02BBState='数据齐可报备',
        target_c02ReportNumber='司农审字[2024]23009190131号',
        update_log_flag=True
    )
